{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#数据库连接\n",
    "from sqlalchemy import create_engine\n",
    "conn = create_engine('mysql+pymysql://root:123456@localhost:3306/keke?charset=utf8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_sql(\"tbl_course\",conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"class_hours\"]=df[\"class_hours\"].str.replace(\",\",\"\").astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df[(df[\"open_time\"]<\"2019-12\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>dependency</th>\n",
       "      <th>credit</th>\n",
       "      <th>lecturer_id</th>\n",
       "      <th>open_time</th>\n",
       "      <th>class_hours</th>\n",
       "      <th>hours_per_week</th>\n",
       "      <th>create_time</th>\n",
       "      <th>last_update_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>大数据导论</td>\n",
       "      <td>计算机基础</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>48</td>\n",
       "      <td>3</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Hadoop大数据技术</td>\n",
       "      <td>java-python-计算机基础</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>64</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>分布式数据库原理与应用</td>\n",
       "      <td>计算机基础/hadoop</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>64</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>10</td>\n",
       "      <td>商务智能方法与应用</td>\n",
       "      <td>java</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2017/12/07 00:00:00.000</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>11</td>\n",
       "      <td>VIP创新实践课程</td>\n",
       "      <td>java/python</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>16</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12</td>\n",
       "      <td>hadoop实战</td>\n",
       "      <td>hadoop</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2017/12/07 00:00:00.000</td>\n",
       "      <td>32</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>9</td>\n",
       "      <td>机器学习</td>\n",
       "      <td>python</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2018/12/02 00:00:00.000</td>\n",
       "      <td>36</td>\n",
       "      <td>4</td>\n",
       "      <td>2019-07-09 12:20:05</td>\n",
       "      <td>2019-10-09 12:20:05</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id         name         dependency  credit  lecturer_id  \\\n",
       "0    1        大数据导论              计算机基础       2            1   \n",
       "1    2  Hadoop大数据技术  java-python-计算机基础       4            1   \n",
       "2    3  分布式数据库原理与应用       计算机基础/hadoop       4            2   \n",
       "8   10    商务智能方法与应用               java       4            3   \n",
       "9   11    VIP创新实践课程        java/python       3            4   \n",
       "10  12     hadoop实战             hadoop       4            3   \n",
       "13   9        机器学习              python       4            4   \n",
       "\n",
       "                  open_time  class_hours  hours_per_week         create_time  \\\n",
       "0       2019-07-09 12:20:05           48               3 2019-07-09 12:20:05   \n",
       "1       2019-07-09 12:20:05           64               4 2019-07-09 12:20:05   \n",
       "2       2019-07-09 12:20:05           64               4 2019-07-09 12:20:05   \n",
       "8   2017/12/07 00:00:00.000           16               4 2019-07-09 12:20:05   \n",
       "9       2019-07-09 12:20:05           16               4 2019-07-09 12:20:05   \n",
       "10  2017/12/07 00:00:00.000           32               4 2019-07-09 12:20:05   \n",
       "13  2018/12/02 00:00:00.000           36               4 2019-07-09 12:20:05   \n",
       "\n",
       "      last_update_time  \n",
       "0  2019-10-09 12:20:05  \n",
       "1  2019-10-09 12:20:05  \n",
       "2  2019-10-09 12:20:05  \n",
       "8  2019-10-09 12:20:05  \n",
       "9  2019-10-09 12:20:05  \n",
       "10 2019-10-09 12:20:05  \n",
       "13 2019-10-09 12:20:05  "
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>计算机基础</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>java</td>\n",
       "      <td>python</td>\n",
       "      <td>计算机基础</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>计算机基础</td>\n",
       "      <td>hadoop</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>java</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>java</td>\n",
       "      <td>python</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>hadoop</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>python</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         0       1      2\n",
       "0    计算机基础    None   None\n",
       "1     java  python  计算机基础\n",
       "2    计算机基础  hadoop   None\n",
       "8     java    None   None\n",
       "9     java  python   None\n",
       "10  hadoop    None   None\n",
       "13  python    None   None"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dependency=df[\"dependency\"].str.replace(\"/\",\" \").str.replace(\"-\",\" \").str.split(\" \",expand=True)\n",
    "df_dependency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0      计算机基础\n",
       "1       java\n",
       "1     python\n",
       "1      计算机基础\n",
       "2      计算机基础\n",
       "2     hadoop\n",
       "8       java\n",
       "9       java\n",
       "9     python\n",
       "10    hadoop\n",
       "13    python\n",
       "dtype: object"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_dependency=df_dependency.stack().reset_index(level=1,drop=True)\n",
    "df_dependency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_dependency.name=\"dependency_new\"\n",
    "df=df.drop([\"dependency\"],axis=1).join(df_dependency)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "========================================================"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=pd.DataFrame(df.groupby(\"dependency_new\")[\"lecturer_id\"].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1=df1[\"lecturer_id\"].rename(\"uniqs\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "================================================================"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=pd.DataFrame(df.groupby([\"dependency_new\"])[\"hours_per_week\"].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2=df2[\"hours_per_week\"].rename(\"total_hours\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.merge(df1,df2,on=\"dependency_new\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=df.sort_values(by= \"total_hours\",ascending = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv(\"C:/Users/HP/Desktop/2AA.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>uniqs</th>\n",
       "      <th>total_hours</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dependency_new</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>java</th>\n",
       "      <td>3</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>python</th>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>计算机基础</th>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hadoop</th>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                uniqs  total_hours\n",
       "dependency_new                    \n",
       "java                3           12\n",
       "python              2           12\n",
       "计算机基础               2           11\n",
       "hadoop              2            8"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
